﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Data.SqlClient;
using System.Speech;
using System.Speech.Recognition;
using System.Device.Location;
using System.IO;
using System.Reflection;
using Microsoft.Office.Interop.Excel;
using System.Diagnostics;

namespace SpeechBill
{
    public partial class Main : Form
    {
        SqlDataAdapter sda = null;
        DataSet ds = null;
            
        private Microsoft.Office.Interop.Excel.Application myExcel = null;  

        private int salary = 0, total = 0;
        private SpeechRecognitionEngine sre = new SpeechRecognitionEngine();
        public Main()
        {
            InitializeComponent();
        }
    
        private void Main_Load(object sender, EventArgs e)
        {
            
            SqlConnection con = DBHelper.GetCon();
            string cmdTest = "select * from consume";
            SqlCommand cmd = new SqlCommand(cmdTest, con);
            sda = new SqlDataAdapter(cmd);
            ds = new DataSet();
            sda.Fill(ds, "consume");
            //2、将数据显示在控件上
            dataGridView1.DataSource = ds.Tables["consume"];


            label1.BackColor = Color.Transparent;
            label2.BackColor = Color.Transparent;
            label3.BackColor = Color.Transparent;
            label4.BackColor = Color.Transparent;
            label6.BackColor = Color.Transparent;
            label1.Text = null;
            label2.Text = null;
            label3.Text = "当前用户:"+Login.uname.ToString();
            label4.Text = null;
            label6.Text = null;
            CurrentPosition cur = new CurrentPosition();
            cur.GetLocationEvent();
        }
        
        /**
         * 处理语音识别的结果，转为string
         */
        void G_SpeechRecognized(object sender, SpeechRecognizedEventArgs e)
        {
            Text = e.Result.Text;
            richTextBox1.Text = Text;
            Speech sp = new Speech();
            sp.str_text = Text;
            sp.ShowDialog();
        }


        /**
         * 实现的语音识别语法
         * 返回Grammar
         **/
        public static Grammar GetSpeechGrammer()
        {
            Choices unit = new Choices(new string[] { "十", "百", "千", "元" });
            //Choices num = new Choices(new string[] { "一","二", "两", "三", "四", "五", "六", "七", "八", "九"});
            Choices num = new Choices(new string[] { "1", "2", "两", "3", "4", "5", "6", "7", "8", "9" });
            
            Choices C_time = new Choices(new string[] { "今天", "昨天" });
            Choices C_stime = new Choices(new string[] { "早上", "中午", "晚上", "宵夜" });
            Choices C_verb = new Choices(new string[] { "花了", "消费" });

            GrammarBuilder devicePhrase = new GrammarBuilder(C_time,0,1);
            devicePhrase.Append(C_stime,0,1);
            devicePhrase.Append(C_verb);
            devicePhrase.Append(num,0,1);
            devicePhrase.Append(unit,0,1);
            devicePhrase.Append(num, 0, 1);
            devicePhrase.Append(unit, 0, 1);
            devicePhrase.Append(num, 0, 1);
            devicePhrase.Append(unit, 0, 1);
            
            Grammar G_str = new Grammar(devicePhrase);
            
            G_str.Name = "Speech Grammer";

            return G_str;

        }


        private void SpeechStartBtn_Click(object sender, EventArgs e)
        {
            Control.CheckForIllegalCrossThreadCalls = false;
            sre.SetInputToDefaultAudioDevice();//默认的语音输入设备

            Grammar G_str = GetSpeechGrammer();
            G_str.SpeechRecognized += new EventHandler<SpeechRecognizedEventArgs>(G_SpeechRecognized);

            sre.LoadGrammar(G_str);
            sre.RecognizeAsync(RecognizeMode.Multiple);//异步调用识别引擎，允许多次识别
        }

        private void 支出分类ToolStripMenuItem_Click(object sender, EventArgs e)
        {
            ClassTend ct = new ClassTend();
            ct.ShowDialog();
        }

        private void 本月支出ToolStripMenuItem_Click(object sender, EventArgs e)
        {

            string time = DateTime.Now.ToString("y");
            
            SqlConnection con = DBHelper.GetCon();
            con.Open();
            String cmdText = "select * from consume";
            SqlCommand cmd = new SqlCommand(cmdText, con);
            SqlDataReader reader = cmd.ExecuteReader();
            while (reader.Read())
            {
                string retime = reader["c_day"].ToString();
                if (retime.Contains(time) == true)
                {
                    salary += int.Parse(reader["c_money"].ToString()); 
                }
                total += int.Parse(reader["c_money"].ToString());
            }
            reader.Close();

            label1.Text = "本月支出:"+salary.ToString()+"元";
            salary = 0;
            

        }
        
        private void button1_Click(object sender, EventArgs e)
        {
            this.Close();
        }

        private void 历史支出ToolStripMenuItem_Click(object sender, EventArgs e)
        {
            label2.Text = "历史支出:" + total.ToString() + "元";
            total = 0;
        }

        private void 修改个人信息ToolStripMenuItem_Click(object sender, EventArgs e)
        {
            Pinformation infor = new Pinformation();
            infor.ShowDialog();
        }

        private void 获取当前地点ToolStripMenuItem_Click(object sender, EventArgs e)
        {
            
            label4.Text = "纬度:" + CurrentPosition.db_Latitude.ToString();
            label6.Text = "经度:" + CurrentPosition.db_Longitude.ToString();
        }

        private void 导出excel表格ToolStripMenuItem_Click(object sender, EventArgs e)
        {
            print(dataGridView1); 
        }
        public void print(DataGridView dataGridView1)
        {
            //导出到execl     
            try
            {
                SaveFileDialog saveFileDialog = new SaveFileDialog();
                saveFileDialog.Filter = "导出Excel (*.xls)|*.xls";
                saveFileDialog.FilterIndex = 0;
                saveFileDialog.RestoreDirectory = true;
                saveFileDialog.CreatePrompt = true;
                saveFileDialog.Title = "导出文件保存路径";
                saveFileDialog.ShowDialog();
                string strName = saveFileDialog.FileName;
                if (strName.Length != 0)
                {
                    //没有数据的话就不往下执行     
                    if (dataGridView1.Rows.Count == 0)
                        return;

                    // toolStripProgressBar1.Visible = true;  
                    System.Reflection.Missing miss = System.Reflection.Missing.Value;
                    //实例化一个Excel.Application对象  
                    Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
                    excel.Application.Workbooks.Add(true);
                    excel.Visible = false;//若是true，则在导出的时候会显示EXcel界面。  
                    if (excel == null)
                    {
                        MessageBox.Show("EXCEL无法启动！", "错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
                        return;
                    }
                    Microsoft.Office.Interop.Excel.Workbooks books = (Microsoft.Office.Interop.Excel.Workbooks)excel.Workbooks;
                    Microsoft.Office.Interop.Excel.Workbook book = (Microsoft.Office.Interop.Excel.Workbook)(books.Add(miss));
                    Microsoft.Office.Interop.Excel.Worksheet sheet = (Microsoft.Office.Interop.Excel.Worksheet)book.ActiveSheet;
                    sheet.Name = "test";
                    int m = 0, n = 0;
                    //生成Excel中列头名称     
                    for (int i = 0; i < dataGridView1.Columns.Count; i++)
                    {
                        excel.Cells[1, i + 1] = dataGridView1.Columns[i].HeaderText;//输出DataGridView列头名  
                    }
                    //把DataGridView当前页的数据保存在Excel中     
                    for (int i = 0; i < dataGridView1.Rows.Count - 1; i++)
                    {
                        for (int j = 0; j < dataGridView1.Columns.Count; j++)
                        {
                            if (dataGridView1[j, i].ValueType == typeof(string))
                            {
                                excel.Cells[i + 2, j + 1] = "'" + dataGridView1[j, i].Value.ToString();
                            }
                            else
                            {
                                excel.Cells[i + 2, j + 1] = dataGridView1[j, i].Value.ToString();
                            }
                        }
                    }
                    sheet.SaveAs(strName, miss, miss, miss, miss, miss, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, miss, miss, miss);
                    book.Close(false, miss, miss);
                    books.Close();
                    excel.Quit();
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(sheet);
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(book);
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(books);
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(excel);
                    GC.Collect();
                    MessageBox.Show("数据已经成功导出!", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
                    // toolStripProgressBar1.Value = 0;  
                    System.Diagnostics.Process.Start(strName);
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "错误提示");
            }
        }

        private void 账单查询ToolStripMenuItem_Click(object sender, EventArgs e)
        {
            selSp sp = new selSp();
            sp.Show();
        }

        private void 保存ToolStripMenuItem_Click(object sender, EventArgs e)
        {
            sda.Update(ds, "consume");
        } 
    }
}
